package view;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.CustComplaint;

public class CustComplaintDAO {

	private DBConnect con = null;
	private long lnoexist;

	public CustComplaintDAO(DBConnect incon) {
		con = incon;
		try {
			con.getConnection().setAutoCommit(false);
		} catch (SQLException e) {

		}
	}

	public CustComplaint CreateComplaint(CustComplaint inCustComplaint) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// insert into cust_complaint table based on order_id as main key
			ps = con.getConnection()
					.prepareStatement(
							"insert into "+con.getSchema()+".CUST_COMPLAINT (ORDER_ID, CUST_REP_ID, COMPLAINT_DATE, COMPLAINT_CD,)"
									+ "values(?,?,sysdate,?)");
			ps.setLong(1, inCustComplaint.getFactoryOrder().getOrderId());
			ps.setLong(2, inCustComplaint.getCustomerRepresentative()
					.getCustRepId());
			ps.setString(3, inCustComplaint.getComplaint().getComplaintCd());

			ps.executeUpdate();
			System.out.println("Addition Success");
			ps.close();
		} catch (SQLException e) {
			System.out.println("Error in Create Cust_Complaint"
					+ e.getSQLState());
			System.out.println("\nError Code: " + e.getErrorCode());
			System.out.println("\nMessage: " + e.getMessage());

			System.exit(1);
		} catch (Exception e) {
			System.out.println("unknown Error in Create Cust_Complaint");
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);
		}

		return null;
	}

	public CustComplaint ViewComplaint(CustComplaint inCustComplaint) {
		StringBuffer custComplaintReport = new StringBuffer(500);
		PreparedStatement ps = null;
		ResultSet rs = null;
		int inOrderID = 0;
		int inCustRepID = 0;
		int inComplaintCD = 0;
		String strComplaint = "";

		try {
			// view cust_complaint based on order_id
			ps = con.getConnection()
					.prepareStatement(
							"SELECT cc.order_id, cc.cust_rep_id,  cc.complaint_date, cc.complaint_cd, co.complaint_desc "
									+ "FROM "+con.getSchema()+".cust_complaint cc, "+con.getSchema()+".complaint co WHERE cc.complaint_cd = co.complaint_cd AND cc.order_id= ? ");

			ps.setLong(1, inCustComplaint.getFactoryOrder().getOrderId());
			rs = ps.executeQuery();
			custComplaintReport.append(String.format(
					"%1$-16s %2$-40s %3$-65s %4$s\n", "Order ID",
					"Cust Rep ID", "Comaplint CD", "Complaint Descripton"));
			while (rs.next()) {

				inOrderID = rs.getInt(1);
				inCustRepID = rs.getInt(2);
				inComplaintCD = rs.getInt(3);
				strComplaint = rs.getString(4);
				String outRow = String.format(
						"%1$-16s %2$-40s %3$-65s %4$d \n", inOrderID,
						inCustRepID, inComplaintCD, strComplaint);
				custComplaintReport.append(outRow);
			}
			System.out.println(custComplaintReport);
			System.out.println("Display Complete");
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.out.println("SQL Error in get Cust Complaint summary"
					+ e.getSQLState());
			System.out.println("\nError Code: " + e.getErrorCode());
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);
		} catch (Exception e) {
			System.out.println("unknown Error in get Cust Complaint summary");
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);
		}
		return null;
	}

	public boolean UpdateComplaint(CustComplaint inCustComplaint) {
		PreparedStatement ps = null;

		System.out.println("Cust Complaint to be Inserted \n");

		try {
			// update cust_complaint table based on order_id
			ps = con.getConnection().prepareStatement(
					"update "+con.getSchema()+".cust_complaint set cust_rep_id=?, complaint_date=sysdate, "
							+ "complaint_cd=? where order_id = ?");
			ps.setLong(1, inCustComplaint.getCustomerRepresentative()
					.getCustRepId());
			ps.setString(2, inCustComplaint.getComplaint().getComplaintCd());
			ps.setLong(3, inCustComplaint.getFactoryOrder().getOrderId());

			ps.executeUpdate();
			System.out.println("Addition Success");
			ps.close();
		} catch (SQLException e) {
			System.out.println("Error in Create Cust Complaint"
					+ e.getSQLState());
			System.out.println("\nError Code: " + e.getErrorCode());
			System.out.println("\nMessage: " + e.getMessage());

			System.exit(1);
		} catch (Exception e) {
			System.out.println("unknown Error in Create Cust Complaint");
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);
		}
		return false;
	}

	public boolean DeleteComplaint(CustComplaint inCustComplaint) {
		PreparedStatement ps = null;

		System.out.println("Cust Complaint to be Deleted \n");

		// delete cust_complaint base on order_id
		try {
			ps = con.getConnection().prepareStatement(
					"delete "+con.getSchema()+".cust_complaint where order_id=?");
			ps.setLong(1, inCustComplaint.getFactoryOrder().getOrderId());
			ps.executeQuery();
			System.out.println("deleted");
			ps.close();
		} catch (SQLException e) {
			System.out.println("Error in Cust Complaint Delete"
					+ e.getSQLState());
			System.out.println("\nError Code: " + e.getErrorCode());
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);

		} catch (Exception e) {
			System.out.println("unknown Error in Cust Complaint delete");
			System.out.println("\nMessage: " + e.getMessage());
			System.exit(1);

		}
		return false;
	}

}
